#include "database.h"
MYSQL* connect_init(MYSQL* conn)
{
    MYSQL* pconn = mysql_init(conn);
    if(pconn == NULL)
    {
        printf("%s", mysql_error(conn));
        exit(1);
    }
    const char * host = "localhost";
    const char * user = "root";
    const char * passwd = "123456";
    const char * db = "out_sch";
    pconn = mysql_real_connect(pconn, host, user, passwd, db, 0, NULL, 0);
    if(pconn == NULL)
    {
        printf("%s", mysql_error(conn));
        mysql_close(conn);
        exit(1);
    }
    return pconn;
}

void Init_database(MYSQL* pconn)
{
    // 使用mysql_query函数创建表
    // 创建科室表
    const char* sql1 = "create table if not exists department("
                        "id int primary key auto_increment,"
                        "name varchar(50) NOT NULL,"
                        "description text"
                        ");";
    // 创建医生表
    const char* sql2 = "create table if not exists doctor("
                        "id int primary key auto_increment,"
                        "name varchar(50) NOT NULL,"
                        "department_id int NOT NULL,"
                        "foreign key (department_id) references department(id)  ON DELETE CASCADE ON UPDATE CASCADE"
                        ");";
    // 创建排班表
    const char* sql3 = "create table if not exists schedule("
                        "id int primary key auto_increment,"
                        "doctor_id int NOT NULL,"
                        "date DATE NOT NULL,"
                        "shift varchar(20) NOT NULL,"
                        "foreign key (doctor_id) references doctor(id)  ON DELETE CASCADE ON UPDATE CASCADE"
                        ");";
    // 创建门诊表
    const char* sql4 = "create table if not exists clinic("
                        "id int primary key auto_increment,"
                        "department_id int NOT NULL,"
                        "name varchar(50) NOT NULL,"
                        "location varchar(50) NOT NULL,"
                        "foreign key (department_id) references department(id)  ON DELETE CASCADE ON UPDATE CASCADE"
                        ");";
    // 创建就诊人表
    const char* sql5 = "create table if not exists patient("
                        "id int primary key auto_increment,"
                        "name varchar(50) NOT NULL,"
                        "gender varchar(10) NOT NULL,"
                        "age int NOT NULL,"
                        "phone varchar(20),"
                        "medical_history TEXT,"
                        "registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
                        ");";
    // 创建挂号表 
    const char* sql6 = 
                        "CREATE TABLE IF NOT EXISTS registrations ("
                        "id INT PRIMARY KEY AUTO_INCREMENT,"
                        "patient_id INT NOT NULL,"
                        "schedule_id INT NOT NULL,"
                        "registration_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
                        "priority varchar(20) NOT NULL DEFAULT '普通',"
                        "status varchar(20) NOT NULL DEFAULT '等待就诊',"
                        "FOREIGN KEY (patient_id) REFERENCES patient(id),"
                        "FOREIGN KEY (schedule_id) REFERENCES schedule(id)"
                        ");";

    // mysql_query 建表
    if (mysql_query(pconn, sql1) != 0) {
        fprintf(stderr, "创建科室表失败: %s\n", mysql_error(pconn));
        exit(1);
    }
    
    if (mysql_query(pconn, sql2) != 0) {
        fprintf(stderr, "创建医生表失败: %s\n", mysql_error(pconn));
        exit(1);
    }
    
    if (mysql_query(pconn, sql3) != 0) {
        fprintf(stderr, "创建排班表失败: %s\n", mysql_error(pconn));
        exit(1);
    }
    
    if (mysql_query(pconn, sql4) != 0) {
        fprintf(stderr, "创建门诊表失败: %s\n", mysql_error(pconn));
        exit(1);
    }
    
    if (mysql_query(pconn, sql5) != 0) {
        fprintf(stderr, "创建就诊人表失败: %s\n", mysql_error(pconn));
        exit(1);
    }

    if (mysql_query(pconn, sql6) != 0) {
        fprintf(stderr, "创建就诊人表失败: %s\n", mysql_error(pconn));
        exit(1);
    }
    printf("表格加载成功！\n");
}

//预处理有关函数
void stmt_check_safe(MYSQL_STMT* stmt , MYSQL* pconn)
{
    printf("error : %s\n", mysql_stmt_error(stmt));
    mysql_stmt_close(stmt);
    mysql_close(pconn);
    exit(1);
}
//stmt到连接的映射
// 功能:传sql语句进来进行模板化
MYSQL_STMT* sql_stl(MYSQL* pconn, const char* sql, int count)
{
    //使用预处理sql语句来将sql语句模板化
    //建立连接到stmt的映射
    MYSQL_STMT* stmt = mysql_stmt_init(pconn);
    if(stmt == NULL)
    {
        printf("error: %s\n", mysql_error(pconn));
        mysql_close(pconn);
        exit(1);
    }
    //预处理初始化 也就是执行prepare操作，将stmt与sql语句进行绑定
    int ret = mysql_stmt_prepare(stmt, sql, strlen(sql));
    if(ret != 0)
    {
        stmt_check_safe(stmt, pconn);
    }
    int count0 = mysql_stmt_param_count(stmt);
    if(count0 != count)
    {
        stmt_check_safe(stmt, pconn);
    }
    return stmt;
}
// bind作为输入参数时参数绑定
void bind_tie_in(MYSQL_BIND* bind, void* buffer, unsigned long* length, _Bool* is_null, enum enum_field_types buffer_type)
{
    bind->buffer = buffer;
    bind->length = length;
    bind->is_null = is_null;
    bind->buffer_type = buffer_type;
}
// bind作为输出参数时参数绑定
void bind_tie_out(MYSQL_BIND* bind_res, void* buffer, unsigned long buffer_length, enum enum_field_types buffer_type)
{
    bind_res->buffer = buffer;
    bind_res->buffer_length = buffer_length;
    bind_res->buffer_type = buffer_type;
}
// 不使用预处理获取结果集
void show_result_ust(MYSQL* pconn)
{
     //通过mysql_store_result()函数获取mysql_query的返回值
    MYSQL_RES * res = mysql_store_result(pconn);
    //获取字段信息，然后打印字段
    //获取字段个数
    int cols = mysql_num_fields(res);
    //逐列获取字段信息
    MYSQL_FIELD* field = mysql_fetch_field(res);
    //循环中打印字段信息
    for(int i = 0; i < cols; i++)
    {
        printf("%s\t", field[i].name);
    }
    printf("\n");
    //加返回值的验证
    if(mysql_num_rows(res) != 0)
    {
        //对数据进行处理
        MYSQL_ROW row;
        //从结果集中拿出一行，如果结果集中没有更多数据或结果集为空，则返回NULL
        while((row = mysql_fetch_row(res)))
        {
            unsigned int colunm;
            colunm = mysql_num_fields(res);
            for(int i = 0; i < colunm; i++)
            {
                printf("%s\t", row[i]);
            }
            printf("\n");
        }
    }
    mysql_free_result(res);
}
// 使用预处理后获取结果集中的元数据及字段信息
MYSQL_RES* show_result_st(MYSQL* pconn, MYSQL_STMT* stmt)
{
    //获取元数据信息
    MYSQL_RES* res = mysql_stmt_result_metadata(stmt);
    if(res == NULL)
    {
        stmt_check_safe(stmt, pconn);
    }
    //获取字段信息，然后打印字段
    //获取字段个数
    int cols = mysql_num_fields(res);
    //逐列获取字段信息
    MYSQL_FIELD* field = mysql_fetch_field(res);
    //循环中打印字段信息
    for(int i = 0; i < cols; i++)
    {
        printf("%s\t", field[i].name);
    }
    printf("\n");
    return res;
}
//模板化的参数占位符绑定数据
void bind_param(MYSQL_STMT* stmt, MYSQL_BIND* bind, MYSQL* pconn)
{
    int ret = mysql_stmt_bind_param(stmt, bind);
    if(ret != 0)
    {
        stmt_check_safe(stmt, pconn);
    }
}
//bind_res与结果集绑定，并打印结果集
void show_bind_res(MYSQL_STMT* stmt, MYSQL_BIND* bind_res, MYSQL* pconn, int* id_res, char* name_res, char* text_res)
{
    int ret = mysql_stmt_bind_result(stmt, bind_res);
    if(ret)
    {
        stmt_check_safe(stmt, pconn);
    }
    //得到数据之后进行缓冲处理，后面使用mysql_stmt_fetch获取行数据
    ret = mysql_stmt_store_result(stmt);
    if(ret)
    {
        stmt_check_safe(stmt, pconn);
    }
    //真正的获取数据
    while(1)
    {
        int status = mysql_stmt_fetch(stmt);
        if(status == 1 || status == MYSQL_NO_DATA)
        {
            break;
        }
        printf("%d\t%s\t%s\n", *id_res, name_res, text_res);
    }
}
//执行模板化的sql语句
void execute_sql(MYSQL_STMT* stmt, MYSQL_BIND* bind, MYSQL* pconn)
{
    int ret = mysql_stmt_execute(stmt);
    if(ret != 0)
    {
        stmt_check_safe(stmt, pconn);
    }
}
// 检查sql语句是否存在结果集
int sql_if_res(MYSQL_STMT* stmt)
{
    //不能使用mysql_stmt_result_metdata,获取的是结果集的元数据，不是实际的结果
    //应使用mysql_stmt_store_result 和 mysql_stmt_num-rows 先缓存结果集然后查看结果集的数量
    if (mysql_stmt_store_result(stmt)) 
    {
        fprintf(stderr, "存储结果失败: %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return 1;
    }

    int num_rows = mysql_stmt_num_rows(stmt);
    if (num_rows == 0) {
        printf("不存在结果集\n");
        mysql_stmt_close(stmt);
        return 1;
    }
    printf("存在结果集\n");
    return 0; 
}

//用户交互函数
//用户输入int类型
void int_in(int* value)
{
    if(scanf("%d", value) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');
}
//用户输入字符串类型
void char_in(char* value, unsigned long* value_len)
{
    fgets(value, sizeof(value), stdin);
    value[strcspn(value, "\n")] = 0;  // 移除换行符
    *value_len = strlen(value);
}
//查询一个参数类型为int数据是否存在
int sql_int_if_have(MYSQL_STMT* stmt, MYSQL* pconn, MYSQL_BIND* bind, int* id)
{

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(bind, id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, bind, pconn);
    //执行sql语句
    execute_sql(stmt, bind, pconn);
    
    if(sql_if_res(stmt))    return 1;

    mysql_stmt_free_result(stmt);
    //释放映射关系
    mysql_stmt_close(stmt);
    return 0;
}

//科室管理函数实现
//打印
void show_departments()
{
    printf("\n===== 科室管理 =====\n");
    printf("1. 添加科室\n");
    printf("2. 查看科室\n");
    printf("3. 更新科室\n");
    printf("4. 删除科室\n");
    printf("0. 退出管理\n");
    printf("=======================\n");
    printf("请输入你的选择: ");
}

void manage_departments(MYSQL* pconn)
{
    int choice;
    do{
        //显示菜单
        show_departments();
        scanf("%d", &choice);
        getchar();  //读走换行符
        switch(choice)
        {
            case 1: 
                add_department(pconn);
                break;
            case 2: 
                view_department(pconn);
                break;
            case 3: 
                update_department(pconn);
                break;
            case 4:
                delete_department(pconn);
                break;
        }
    }while(choice !=0);

}

// 增加科室
void add_department(MYSQL* pconn)
{
    // //使用预处理sql语句来将sql语句模板化
    // //建立连接到stmt的映射
    // MYSQL_STMT* stmt = mysql_stmt_init(pconn);
    // if(stmt == NULL)
    // {
    //     printf("error: %s\n", mysql_error(pconn));
    //     mysql_close(pconn);
    //     exit(1);
    // }
    const char* sql = "insert into department (name, description) values(?, ?)";
    // //预处理初始化 也就是执行prepare操作，将stmt与sql语句进行绑定
    // int ret = mysql_stmt_prepare(stmt, sql, strlen(sql));
    // if(ret != 0)
    // {
    //     stmt_check_safe(stmt, pconn);
    // }
    // int count = mysql_stmt_param_count(stmt);
    // if(count != 2)
    // {
    //     stmt_check_safe(stmt, pconn);
    // }

    MYSQL_STMT* stmt = sql_stl(pconn, sql, 2);

    // 每一个参数对应一个bind,定义参数
    MYSQL_BIND bind[2];

    char name[50] = {0};
    unsigned long name_len = 0;

    char textvalue[256] = {0};
    unsigned long text_len = 0;

    //绑定参数
    memset(bind, 0, sizeof(bind));

    // bind[0].buffer = name;
    // bind[0].length = &name_len;
    // bind[0].buffer_type = MYSQL_TYPE_VAR_STRING;
    // bind[0].is_null = 0;
    bind_tie_in(&bind[0], name, &name_len, NULL, MYSQL_TYPE_VAR_STRING);

    // bind[1].buffer = textvalue;
    // bind[1].length = &text_len;
    // bind[1].is_null = 0;
    // bind[1].buffer_type = MYSQL_TYPE_BLOB;
    bind_tie_in(&bind[1], textvalue, &text_len, NULL, MYSQL_TYPE_BLOB);

    //模板化的参数占位符绑定数据
    // int ret = mysql_stmt_bind_param(stmt, bind);
    // if(ret != 0)
    // {
    //     stmt_check_safe(stmt, pconn);
    // }

    bind_param(stmt, bind, pconn);

    //接收用户参数 传递参数
    printf("请输入科室名称：");
    fgets(name, sizeof(name), stdin);
    name[strcspn(name, "\n")] = 0;  // 移除换行符

    printf("请输入科室描述：");
    fgets(textvalue, sizeof(textvalue), stdin);
    textvalue[strcspn(textvalue, "\n")] = 0;  // 移除换行符

    name_len = strlen(name);
    text_len = strlen(textvalue);

    //执行模板化的sql语句
    // ret = mysql_stmt_execute(stmt);
    // if(ret != 0)
    // {
    //     stmt_check_safe(stmt, pconn);
    // }
    execute_sql(stmt, bind, pconn);
    printf("添加%s科室成功！\n", name);
}

// 查看已有科室
// 这里设计查询全部科室
void view_department(MYSQL* pconn)
{
    const char* sql = "select * from department";

    //发送sql语句
    mysql_query(pconn, sql);
    // //通过mysql_store_result()函数获取mysql_query的返回值
    // MYSQL_RES * res = mysql_store_result(pconn);
    // //加返回值的验证
    // if(mysql_num_rows(res) != 0)
    // {
    //     //对数据进行处理
    //     MYSQL_ROW row;
    //     //从结果集中拿出一行，如果结果集中没有更多数据或结果集为空，则返回NULL
    //     while((row = mysql_fetch_row(res)))
    //     {
    //         unsigned int colunm;
    //         colunm = mysql_num_fields(res);
    //         for(int i = 0; i < colunm; i++)
    //         {
    //             printf("%s\t", row[i]);
    //         }
    //         printf("\n");
    //     }
    // }
    // mysql_free_result(res);
    show_result_ust(pconn);
}

// 更新科室信息
void update_department(MYSQL* pconn)
{
    //总体的思路：用户输入要修改科室的id，查询id，如果不存在return不做操作，如果存在，获取用户输入的科室信息，更新
    int id;
    printf("请输入要更新科室的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from department where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    //获取sql语句的执行结果
    //使用了预处理所以要使用预处理api来获取结果集
    
    // //先获取元数据信息及字段信息
    // MYSQL_RES* res = show_result_st(pconn, stmt);
    // //设置输出参数 ,后续每行的数据都放在的参数中
    // int id_res;
    // char name_res[50];
    // char description_res[256];

    //每个参数对应一个bind_res
    // MYSQL_BIND bind_res[3];

    // memset(bind_res, 0, sizeof(bind_res));
    // //参数绑定
    // bind_tie_out(&bind_res[0], &id_res, sizeof(int), MYSQL_TYPE_LONG);
    // bind_tie_out(&bind_res[1], &name_res, sizeof(name_res), MYSQL_TYPE_VAR_STRING);
    // bind_tie_out(&bind_res[2], &description_res, sizeof(description_res), MYSQL_TYPE_BLOB);
    //若不存在就返回
    if(sql_if_res(stmt))    return;
    // show_bind_res(stmt, bind_res, pconn, &id_res, name_res, description_res);
    mysql_stmt_free_result(stmt);
    //释放映射关系
    mysql_stmt_close(stmt);
    
    //既然存在，准备更新语句
    const char* update_sql = "update department set name=?,description=? where id=?";
    // mysql_query(pconn, update_sql);
    stmt = sql_stl(pconn, update_sql, 3);

    //有三个参数
    MYSQL_BIND update_bind[3];
    memset(update_bind, 0, sizeof(update_bind));

    char name_update[50] = {};
    unsigned long name_updete_len = 0;
    char description_update[256] = {};
    unsigned long description_update_len = 0;

    //绑定参数
    bind_tie_in(&update_bind[0], name_update, &name_updete_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&update_bind[1], description_update, &description_update_len, NULL, MYSQL_TYPE_BLOB);
    bind_tie_in(&update_bind[2], &id, NULL, NULL, MYSQL_TYPE_LONG);

    bind_param(stmt, update_bind, pconn);
    //获取用户数据
    printf("请输入新的科室名称(留空不修改)：");
    if (fgets(name_update, sizeof(name_update), stdin) == NULL) {
        printf("输入错误!\n");
        mysql_stmt_close(stmt);
        return;
    }
    name_update[strcspn(name_update, "\n")] = 0;  // 移除换行符
    name_updete_len = strlen(name_update);
    printf("%s\n", name_update);
    printf("请输入新的科室描述(留空不修改): ");
    if (fgets(description_update, sizeof(description_update), stdin) == NULL) {
        printf("输入错误!\n");
        mysql_stmt_close(stmt);
        return;
    }
    description_update[strcspn(description_update, "\n")] = 0;  // 移除换行符
    description_update_len = strlen(description_update);
    printf("%s\n", description_update);

    // 执行更新
    execute_sql(stmt, update_bind, pconn);
    if (mysql_stmt_execute(stmt) != 0)
    {
        if (mysql_errno(pconn) == 1062) {  // 唯一键冲突
            printf("错误: 科室名称 '%s' 已存在!\n", name_update);
        } else {
            stmt_check_safe(stmt, pconn);
        }
    } 
    else
    {
        printf("科室ID %d 信息更新成功!\n", id);
    }
    mysql_stmt_close(stmt);
}
   
// 删除科室
void delete_department(MYSQL* pconn)
{
    //总体思路:先读取用户输入数据，查看是否存在要删除的科室，然后执行删除操作
    int id;
    printf("请输入要删除科室的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from department where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);

    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    mysql_stmt_close(stmt);

    //准备删除科室操作
    const char* sql_del = "delete from department where id=?";
    //完成映射并将sql语句模板化
    stmt = sql_stl(pconn, sql_del, 1);

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    printf("删除ID为%d科室 成功！\n", id);
    mysql_stmt_close(stmt);
}

//医生管理有关函数
void show_doctors()
{
    printf("\n===== 医生管理 =====\n");
    printf("1. 添加医生\n");
    printf("2. 查看医生\n");
    printf("3. 修改医生\n");
    printf("4. 删除医生\n");
    printf("0. 退出管理\n");
    printf("=======================\n");
    printf("请输入你的选择: ");
}

void manage_doctors(MYSQL* pconn)
{
    int choice;
    do{
        //显示菜单
        show_doctors();
        if(scanf("%d", &choice) != 1)
        {
            printf("输入无效，请输入数字！\n");
            while(getchar() != '\n');   //清除输入缓冲区
            return;
        }
        getchar();
        switch(choice)
        {
            case 1: 
                add_doctor(pconn);
                break;
            case 2: 
                view_doctor(pconn);
                break;
            case 3: 
                update_doctor(pconn);
                break;
            case 4:
                delete_doctor(pconn);
                break;
        }
    }while(choice !=0 );

}

//添加医生
void add_doctor(MYSQL* pconn)
{
    const char* sql = "insert into doctor(name, department_id) values(?,?)";
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 2);

    // 每一个参数对应一个bind,定义参数
    MYSQL_BIND bind[2];

    char name[50] = {0};
    unsigned long name_len = 0;

    int department_id;

    //绑定参数
    memset(bind, 0, sizeof(bind));

    bind_tie_in(&bind[0], name, &name_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&bind[1], &department_id, NULL, NULL, MYSQL_TYPE_LONG);

    bind_param(stmt, bind, pconn);

    //接收用户参数 传递参数
    printf("请输入要添加的医生姓名：");
    fgets(name, sizeof(name), stdin);
    name[strcspn(name, "\n")] = 0;  // 移除换行符

    printf("请输入新医生所在科室：");
    if(scanf("%d", &department_id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    name_len = strlen(name);

    execute_sql(stmt, bind, pconn);
    printf("添加%s医生成功！\n", name);
    mysql_stmt_close(stmt);
}

//查看所有医生
void view_doctor(MYSQL* pconn)
{
    const char* sql = "select * from doctor";
    //发送sql语句
    mysql_query(pconn, sql);
    show_result_ust(pconn);
}

//修改医生信息
void update_doctor(MYSQL* pconn)
{
    //总体的思路：用户输入要修改医生的id，查询id，如果不存在return不做操作，如果存在，获取用户输入的医生信息，更新
    int id;
    printf("请输入要更新医生的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from doctor where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    
    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    //释放映射关系
    mysql_stmt_close(stmt);
    
    //既然存在，准备更新语句
    const char* update_sql = "update doctor set name=?,department_id=? where id=?";
    stmt = sql_stl(pconn, update_sql, 3);

    //有三个参数
    MYSQL_BIND update_bind[3];
    memset(update_bind, 0, sizeof(update_bind));

    char name_update[50] = {};
    unsigned long name_updete_len = 0;
    int department_id = 0;

    //绑定参数
    bind_tie_in(&update_bind[0], name_update, &name_updete_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&update_bind[1], &department_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&update_bind[2], &id, NULL, NULL, MYSQL_TYPE_LONG);

    bind_param(stmt, update_bind, pconn);
    //获取用户数据
    printf("请输入新的医生名称(留空不修改)：");
    if (fgets(name_update, sizeof(name_update), stdin) == NULL) {
        printf("输入错误!\n");
        mysql_stmt_close(stmt);
        return;
    }
    name_update[strcspn(name_update, "\n")] = 0;  // 移除换行符
    name_updete_len = strlen(name_update);

    printf("请输入新的医生所在科室ID: ");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    // 执行更新
    execute_sql(stmt, update_bind, pconn);
    if (mysql_stmt_execute(stmt) != 0)
    {
        if (mysql_errno(pconn) == 1062) {  // 唯一键冲突
            printf("错误: 医生姓名 '%s' 已存在!\n", name_update);
        } else {
            stmt_check_safe(stmt, pconn);
        }
    } 
    else
    {
        printf("ID为%d的医生 信息更新成功!\n", id);
    }
    mysql_stmt_close(stmt);
}

//删除医生
void delete_doctor(MYSQL* pconn)
{
    //总体思路:先读取用户输入数据，查看是否存在要删除的医生，然后执行删除操作
    int id;
    printf("请输入要删除医生的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from department where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    //若不存在就返回

    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    mysql_stmt_close(stmt);

    //准备删除医生操作
    const char* sql_del = "delete from department where id=?";
    //完成映射并将sql语句模板化
    stmt = sql_stl(pconn, sql_del, 1);

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    printf("删除ID为%d的医生 成功！\n", id);
    mysql_stmt_close(stmt);
}

//排班管理相关操作
void show_schedules()
{
    printf("\n===== 排班管理 =====\n");
    printf("1. 添加排班\n");
    printf("2. 查看排班\n");
    printf("3. 修改排班\n");
    printf("4. 删除排班\n");
    printf("0. 退出管理\n");
    printf("=======================\n");
    printf("请输入你的选择: ");
}

void manage_schedules(MYSQL* pconn)
{
    int choice;
    do{
        //显示菜单
        show_schedules();
        scanf("%d", &choice);
        getchar();  //读走换行符
        switch(choice)
        {
            case 1: 
                add_schedule(pconn);
                break;
            case 2: 
                view_schedule(pconn);
                break;
            case 3: 
                update_schedule(pconn);
                break;
            case 4:
                delete_schedule(pconn);
                break;
        }
    }while(choice !=0);

}
void add_schedule(MYSQL* pconn)
{
    const char* sql = "insert into schedule(doctor_id, date, shift) values(?,?,?)";
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 3);

    // 每一个参数对应一个bind,定义参数
    MYSQL_BIND bind[3];

    int doctor_id = 0;

    MYSQL_TIME date;
    memset(&date, 0, sizeof(date)); // 初始化结构体
    date.time_type = MYSQL_TIMESTAMP_DATE;
    
    char shift[50];
    unsigned long shift_len = 0;

    //绑定参数
    memset(bind, 0, sizeof(bind));

    bind_tie_in(&bind[0], &doctor_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&bind[1], (void*)&date, NULL, NULL, MYSQL_TYPE_DATE);
    bind_tie_in(&bind[2], shift, &shift_len, NULL, MYSQL_TYPE_VAR_STRING);

    bind_param(stmt, bind, pconn);

    //接收用户参数 传递参数
    printf("请输入要排班的医生id：");
    if(scanf("%d", &doctor_id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    printf("请输入医生排班日期（eg:YYYY-MM-DD）：");
    scanf("%d-%d-%d", &date.year, &date.month, &date.day);
    getchar();

    printf("请输入值班所在时间段(eg:上午 下午 或者 晚上)：");
    fgets(shift, sizeof(shift), stdin);
    shift[strcspn(shift, "\n")] = 0;  // 移除换行符
    shift_len = strlen(shift);

    execute_sql(stmt, bind, pconn);
    printf("添加ID为%d医生的值班信息成功！\n", doctor_id);

    mysql_stmt_close(stmt);
}

// 查看值班表
void view_schedule(MYSQL* pconn)
{
    const char* sql = "select * from schedule";
    //发送sql语句
    mysql_query(pconn, sql);
    show_result_ust(pconn);
}

void update_schedule(MYSQL* pconn)
{
    int id;
    printf("请输入要修改的排班ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from schedule where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    
    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    //释放映射关系
    mysql_stmt_close(stmt);
    
    //既然存在，准备更新语句
    const char* update_sql = "update schedule set doctor_id=?,date=?, shift=? where id=?";
    stmt = sql_stl(pconn, update_sql, 4);

    //有三个参数
    MYSQL_BIND update_bind[4];
    memset(update_bind, 0, sizeof(update_bind));

    int doctor_id = 0;
    MYSQL_TIME date;

    char shift[50] = {};
    unsigned long shift_len = 0;

    //绑定参数
    bind_tie_in(&update_bind[0], &doctor_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&update_bind[1], (void*)&date, NULL, NULL, MYSQL_TYPE_DATE);
    bind_tie_in(&update_bind[2], shift, &shift_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&update_bind[3], &id, NULL, NULL, MYSQL_TYPE_LONG);

    bind_param(stmt, update_bind, pconn);

    printf("请输入要排班的医生id：");
    if(scanf("%d", &doctor_id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    printf("请输入医生排班日期（eg:2000-5-12）：");
    scanf("%d-%d-%d", &date.year, &date.month, &date.day);


    //获取用户数据
    printf("请输入值班医生所在时间段（eg: 上午 下午 或者 晚上 留空不修改)：");
    if (fgets(shift, sizeof(shift), stdin) == NULL) {
        printf("输入错误!\n");
        mysql_stmt_close(stmt);
        return;
    }
    shift[strcspn(shift, "\n")] = 0;  // 移除换行符
    shift_len = strlen(shift);

    // 执行更新
    execute_sql(stmt, update_bind, pconn);
    if (mysql_stmt_execute(stmt) != 0)
    {
            stmt_check_safe(stmt, pconn);
    } 
    else
    {
        printf("ID为%d的值班计划 信息更新成功!\n", id);
    }
    mysql_stmt_close(stmt);
}

void delete_schedule(MYSQL* pconn)
{
    int id;
    printf("请输入要删除排班信息的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from schedule where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    //若不存在就返回
    //不能使用mysql_stmt_result_metdata,获取的是结果集的元数据，不是实际的结果
    //应使用mysql_stmt_store_result 和 mysql_stmt_num-rows 先缓存结果集然后查看结果集的数量
    if (mysql_stmt_store_result(stmt)) 
    {
        fprintf(stderr, "存储结果失败: %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return;
    }

    int num_rows = mysql_stmt_num_rows(stmt);
    if (num_rows == 0) {
        printf("ID为%d的排班计划不存在\n", id);
        mysql_stmt_close(stmt);
        return;
    }
    printf("ID为%d的排班计划 存在\n",id);
    mysql_stmt_free_result(stmt);
    mysql_stmt_close(stmt);

    //准备删除排班计划操作
    const char* sql_del = "delete from schedule where id=?";
    //完成映射并将sql语句模板化
    stmt = sql_stl(pconn, sql_del, 1);

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    printf("删除ID为%d的排班计划 成功！\n", id);
    mysql_stmt_close(stmt);
}

//科室有关函数
void show_clinics()
{
    printf("\n===== 门诊管理 =====\n");
    printf("1. 添加门诊信息\n");
    printf("2. 查看门诊信息\n");
    printf("3. 更新门诊信息\n");
    printf("4. 删除门诊信息\n");
    printf("0. 退出门诊管理\n");
    printf("=======================\n");
    printf("请输入你的选择: ");
}
void manage_clinics(MYSQL* pconn)
{
    int choice;
    do{
        //显示菜单
        show_clinics();
        scanf("%d", &choice);
        getchar();  //读走换行符
        switch(choice)
        {
            case 1: 
                add_clinic(pconn);
                break;
            case 2: 
                view_clinic(pconn);
                break;
            case 3: 
                update_clinic(pconn);
                break;
            case 4:
                delete_clinic(pconn);
                break;
        }
    }while(choice !=0);
}
void add_clinic(MYSQL* pconn)
{
    const char* sql = "insert into clinic(department_id, name, location) values(?,?,?)";
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 3);

    // 每一个参数对应一个bind,定义参数
    MYSQL_BIND bind[3];

    int department_id = 0;

    char name[50] = {0};
    unsigned long name_len = 0;

    char location[50] ={0};
    unsigned long location_len = 0;

    //绑定参数
    memset(bind, 0, sizeof(bind));

    bind_tie_in(&bind[0], &department_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&bind[1], name, &name_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&bind[2], location, &location_len, NULL, MYSQL_TYPE_VAR_STRING);

    bind_param(stmt, bind, pconn);

    //接收用户参数 传递参数
    printf("请输入要添加的门诊所在科室的id：");
    if(scanf("%d", &department_id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    printf("请输入新加科室名称：");
    fgets(name, sizeof(name), stdin);
    name[strcspn(name, "\n")] = 0;  // 移除换行符
    name_len = strlen(name);

    printf("请输入新加科室所在房间：");
    fgets(location, sizeof(location), stdin);
    location[strcspn(location, "\n")] = 0;  // 移除换行符
    location_len = strlen(location);

    execute_sql(stmt, bind, pconn);
    printf("添加%s科室成功！\n", name);
    mysql_stmt_close(stmt);
}

void view_clinic(MYSQL* pconn)
{
    const char* sql = "select * from clinic";
    //发送sql语句
    mysql_query(pconn, sql);
    show_result_ust(pconn);
}

void update_clinic(MYSQL* pconn)
{
    int id;
    printf("请输入要修改科室的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from clinic where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    
    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    //释放映射关系
    mysql_stmt_close(stmt);
    
    //既然存在，准备更新语句
    const char* update_sql = 
        "update clinic set department_id=?,name=?, location=? where id=?";
    stmt = sql_stl(pconn, update_sql, 4);

    //有三个参数
    MYSQL_BIND update_bind[4];
    memset(update_bind, 0, sizeof(update_bind));
    
    int department_id_update = 0;

    char name_update[50] = {0};
    unsigned long name_updete_len = 0;

    char location_update[50] = {0};
    unsigned long location_update_len = 0;

    //绑定参数
    bind_tie_in(&update_bind[0], &department_id_update, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&update_bind[1], name_update, &name_updete_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&update_bind[2], location_update, &location_update_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&update_bind[3], &id, NULL, NULL, MYSQL_TYPE_LONG);

    bind_param(stmt, update_bind, pconn);

    //获取用户数据
    printf("请输入要修改门诊所在科室ID: ");
    if(scanf("%d", &department_id_update) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    getchar();

    printf("请输入修改科室名称为：");
    if (fgets(name_update, sizeof(name_update), stdin) == NULL) {
        printf("输入错误!\n");
        mysql_stmt_close(stmt);
        return;
    }
    name_update[strcspn(name_update, "\n")] = 0;  // 移除换行符
    name_updete_len = strlen(name_update);

    printf("请输入要修改的门诊所在房间（eg:2201）: ");
    if (fgets(location_update, sizeof(location_update), stdin) == NULL) {
        printf("输入错误!\n");
        mysql_stmt_close(stmt);
        return;
    }
    location_update[strcspn(location_update, "\n")] = 0;  // 移除换行符
    location_update_len = strlen(location_update);

    // 执行更新
    execute_sql(stmt, update_bind, pconn);
    if (mysql_stmt_execute(stmt) != 0)
    {
        stmt_check_safe(stmt, pconn);
    } 
    else
    {
        printf("ID为%d的门诊 信息更新成功!\n", id);
    }
    mysql_stmt_close(stmt);
}
void delete_clinic(MYSQL* pconn)
{
    int id;
    printf("请输入要删除门诊的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from clinic where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    //若不存在就返回

    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    mysql_stmt_close(stmt);

    //准备删除医生操作
    const char* sql_del = "delete from clinic where id=?";
    //完成映射并将sql语句模板化
    stmt = sql_stl(pconn, sql_del, 1);

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    printf("删除ID为%d的门诊 成功！\n", id);
    mysql_stmt_close(stmt);
}

//就诊人相关函数
void show_patients()
{
    printf("\n===== 门诊人管理 =====\n");
    printf("1. 添加就诊人信息\n");
    printf("2. 查看就诊人信息\n");
    printf("3. 更新就诊人信息\n");
    printf("4. 删除就诊人信息\n");
    printf("0. 退出就诊人管理\n");
    printf("=======================\n");
    printf("请输入你的选择: ");
}
void manage_patients(MYSQL* pconn)
{
    int choice;
    do{
        //显示菜单
        show_patients();
        scanf("%d", &choice);
        getchar();  //读走换行符
        switch(choice)
        {
            case 1: 
                add_patient(pconn);
                break;
            case 2: 
                view_patient(pconn);
                break;
            case 3: 
                update_patient(pconn);
                break;
            case 4:
                delete_patient(pconn);
                break;
        }
    }while(choice !=0);
}

void show_all_add_patient(MYSQL* pconn)
{
    const char* sql = "select * from patient";
    //发送sql语句
    mysql_query(pconn, sql);
    show_result_ust(pconn);
}

//觉得这里应该可以查看病人id
void add_patient(MYSQL* pconn)
{
    const char* sql = "insert into patient(name, gender, age, phone, medical_history) values(?,?,?,?,?)";
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 5);

    // 每一个参数对应一个bind,定义参数
    MYSQL_BIND bind[5];

    char name[50] = {0};
    unsigned long name_len = 0;
    
    char gender[10]= {0};
    unsigned long gender_len = 0;

    int age = 0;

    char phone[20] = {0};
    unsigned long phone_len = 0;

    char medical_history[256] ={0};
    unsigned long medical_history_len = 0;

    //绑定参数
    memset(bind, 0, sizeof(bind));

    bind_tie_in(&bind[0], name, &name_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&bind[1], gender, &gender_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&bind[2], &age, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&bind[3], phone, &phone_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&bind[4], medical_history, &medical_history_len, NULL, MYSQL_TYPE_BLOB);

    bind_param(stmt, bind, pconn);

    //接收用户参数 传递参数
    printf("请输入就诊人名称：");
    fgets(name, sizeof(name), stdin);
    name[strcspn(name, "\n")] = 0;  // 移除换行符
    name_len = strlen(name);

    printf("请输入就诊人性别：");
    fgets(gender, sizeof(gender), stdin);
    gender[strcspn(gender, "\n")] = 0;  // 移除换行符
    gender_len = strlen(gender);

    printf("请输入要添加的就诊人年龄：");
    if(scanf("%d", &age) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    printf("请输入就诊人手机号：");
    fgets(phone, sizeof(phone), stdin);
    phone[strcspn(phone, "\n")] = 0;  // 移除换行符
    phone_len = strlen(phone);

    printf("请输入就诊人的药物历史：");
    fgets(medical_history, sizeof(medical_history), stdin);
    medical_history[strcspn(medical_history, "\n")] = 0;  // 移除换行符
    medical_history_len = strlen(medical_history);

    execute_sql(stmt, bind, pconn);
    mysql_stmt_close(stmt);

    printf("添加就诊人%s成功！", name);
    printf("这是所有信息，注意查收！\n");
    show_all_add_patient(pconn);
}

//查询病人信息打算使用预处理的方式，来进行就诊人的数据信息
void view_patient(MYSQL* pconn)
{
    const char* sql = "select id, name, gender, age, phone, medical_history from patient where id = ?";
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);
    //定义输入参数
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));
    int id = 0;

    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //传输参数
    printf("请输入要查询就诊人的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //执行sql语句
    execute_sql(stmt, &bind, pconn);

    //获取sql语句的执行结果
    // 使用了预处理所以要使用预处理api来获取结果集
    
    //先获取元数据信息及字段信息
    MYSQL_RES* res = show_result_st(pconn, stmt);
    //设置输出参数 ,后续每行的数据都放在的参数中
    int id_res;
    char name_res[50] = {0};
    char gender_res[50] = {0};
    int age_res;
    char phone_res[50] = {0};
    char medical_history_res[256] = {0};

    // 每个参数对应一个bind_res
    MYSQL_BIND bind_res[6];

    memset(bind_res, 0, sizeof(bind_res));
    //参数绑定
    bind_tie_out(&bind_res[0], &id_res, sizeof(int), MYSQL_TYPE_LONG);
    bind_tie_out(&bind_res[1], name_res, sizeof(name_res), MYSQL_TYPE_VAR_STRING);
    bind_tie_out(&bind_res[2], gender_res, sizeof(gender_res), MYSQL_TYPE_VAR_STRING);
    bind_tie_out(&bind_res[3], &age_res, sizeof(int), MYSQL_TYPE_LONG);
    bind_tie_out(&bind_res[4], phone_res, sizeof(phone_res), MYSQL_TYPE_VAR_STRING);
    bind_tie_out(&bind_res[5], medical_history_res, sizeof(medical_history_res), MYSQL_TYPE_BLOB);
    
    bind_param(stmt, bind_res, pconn);

    int ret = mysql_stmt_bind_result(stmt, bind_res);
    if(ret)
    {
        stmt_check_safe(stmt, pconn);
    }
    //得到数据之后进行缓冲处理，后面使用mysql_stmt_fetch获取行数据
    ret = mysql_stmt_store_result(stmt);
    if(ret)
    {
        stmt_check_safe(stmt, pconn);
    }
    //真正的获取数据
    while(1)
    {
        int status = mysql_stmt_fetch(stmt);
        if(status == 1 || status == MYSQL_NO_DATA)
        {
            break;
        }
        printf("%d\t%s\t%s\t%d\t%s\t%s\n", id_res, name_res, gender_res, age_res, phone_res, medical_history_res);
    }
    mysql_free_result(res);
    mysql_stmt_close(stmt);
}

void update_patient(MYSQL* pconn)
{
    int id;
    printf("请输入要修改就诊人的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from patient where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    
    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    //释放映射关系
    mysql_stmt_close(stmt);
    
    //既然存在，准备更新语句
    const char* update_sql = 
        "update patient set name=?,gender=?, age=?, phone=?, medical_history=? where id=?";
    stmt = sql_stl(pconn, update_sql, 6);

    //有三个参数
    MYSQL_BIND update_bind[6];
    memset(update_bind, 0, sizeof(update_bind));
    
    char name_update[50] = {0};
    unsigned long name_update_len = 0;
    
    char gender_update[10] = {0};
    unsigned long gender_update_len = 0;

    int age_update = 0;

    char phone_update[20] = {0};
    unsigned long phone_update_len = 0;
    
    char medical_history_update[256] = {0};
    unsigned long medical_history_update_len = 0;


    char location_update[50] = {0};
    unsigned long location_update_len = 0;

    //绑定参数
    bind_tie_in(&update_bind[0], name_update, &name_update_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&update_bind[1], gender_update, &gender_update_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&update_bind[2], &age_update, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&update_bind[3], phone_update, &phone_update_len, NULL, MYSQL_TYPE_VAR_STRING);
    bind_tie_in(&update_bind[4], medical_history_update, &medical_history_update_len, NULL, MYSQL_TYPE_BLOB);
    bind_tie_in(&update_bind[5], &id, NULL, NULL, MYSQL_TYPE_LONG);

    bind_param(stmt, update_bind, pconn);

    //获取用户数据
    //接收用户参数 传递参数
    printf("请输入就诊人名称：");
    fgets(name_update, sizeof(name_update), stdin);
    name_update[strcspn(name_update, "\n")] = 0;  // 移除换行符
    name_update_len = strlen(name_update);

    printf("请输入就诊人性别：");
    fgets(gender_update, sizeof(gender_update), stdin);
    gender_update[strcspn(gender_update, "\n")] = 0;  // 移除换行符
    gender_update_len = strlen(gender_update);

    printf("请输入要添加的就诊人年龄：");
    if(scanf("%d", &age_update) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    printf("请输入就诊人手机号：");
    fgets(phone_update, sizeof(phone_update), stdin);
    phone_update[strcspn(phone_update, "\n")] = 0;  // 移除换行符
    phone_update_len = strlen(phone_update);

    printf("请输入就诊人的药物历史：");
    fgets(medical_history_update, sizeof(medical_history_update), stdin);
    medical_history_update[strcspn(medical_history_update, "\n")] = 0;  // 移除换行符
    medical_history_update_len = strlen(medical_history_update);

    // 执行更新
    execute_sql(stmt, update_bind, pconn);
    if (mysql_stmt_execute(stmt) != 0)
    {
        stmt_check_safe(stmt, pconn);
    } 
    else
    {
        printf("ID为%d的就诊人 信息更新成功!\n", id);
    }
    mysql_stmt_close(stmt);
}
void delete_patient(MYSQL* pconn)
{
    int id;
    printf("请输入要删除就诊人的ID：");
    if(scanf("%d", &id) != 1)
    {
        printf("输入无效，请输入数字！\n");
        while(getchar() != '\n');   //清除输入缓冲区
        return;
    }
    while(getchar() != '\n');

    //检查科室是否存在
    const char* sql = "select id from patient where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    //若不存在就返回

    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    mysql_stmt_close(stmt);

    //准备删除
    const char* sql_del = "delete from patient where id=?";
    //完成映射并将sql语句模板化
    stmt = sql_stl(pconn, sql_del, 1);

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    printf("删除ID为%d的就诊人 成功！\n", id);
    mysql_stmt_close(stmt);
}

// 挂号表相关函数
void show_registrations()
{
    printf("\n===== 挂号管理 =====\n");
    printf("1. 添加挂号信息\n");
    printf("2. 查看挂号信息\n");
    printf("3. 删除挂号信息\n");
    printf("0. 退出管理\n");
    printf("=======================\n");
    printf("请输入你的选择: ");
}

void manage_registrations(MYSQL* pconn)
{
    int choice;
    do{
        //显示菜单
        show_registrations();
        scanf("%d", &choice);
        getchar();  //读走换行符
        switch(choice)
        {
            case 1: 
                add_registration(pconn);
                break;
            case 2: 
                view_registration(pconn);
                break;
            case 3:
                delete_registration(pconn);
                break;
        }
    }while(choice !=0);
}

// 挂号表的设计思路：先要求填写就诊人信息，如果输入名字后会检测是否存在，不存在就先创建就诊人信息，
// 选择科室也就是执行打印所有科室表，然后看科室是否存在，存在就所在科室的值班表，输入值班表id，判断id是否存在，然后设置优先级，时间戳
void add_registration(MYSQL* pconn)
{
    int patient_id = 0;
    printf("请输入就诊人ID:");
    int_in(&patient_id);
    
    const char* pat_id_sql = "select id from patient where id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, pat_id_sql, 1);
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));
    //查看是否存在该患者
    int ret = sql_int_if_have(stmt, pconn, &bind, &patient_id);
    //不存在就去填写就诊人的信息
    if(ret)
    {
        add_patient(pconn);
        printf("请您重新输入您的新ID：");
        int_in(&patient_id);
    }
    //查询所有科室
    view_department(pconn);

    //输入要选择的科室id
    int department_id = 0;
    printf("请输入要挂号的ID:");
    int_in(&department_id);


    const char* dep_id_sql = "select id from department where id = ?";
    //完成映射并将sql语句模板化
    stmt = sql_stl(pconn, dep_id_sql, 1);
    memset(&bind, 0, sizeof(bind));
    //查看是否存在该科室
    ret = sql_int_if_have(stmt, pconn, &bind, &department_id);
    if(ret)
    {
        return;
    }

    const char* show_sch_sql = 
                    "SELECT s.id AS 值班表ID, d.name AS 医生姓名, dept.name AS 科室名称, "
                    "s.date AS 值班日期, s.shift AS 班次 "
                    "FROM schedule s "
                    "JOIN doctor d ON s.doctor_id = d.id "
                    "JOIN department dept ON d.department_id = dept.id "
                    "WHERE dept.id = ? "
                    "ORDER BY s.date";

    stmt = sql_stl(pconn, show_sch_sql, 1);

    MYSQL_BIND bind_id;
    memset(&bind_id, 0, sizeof(bind_id));
    
    bind_tie_in(&bind_id, &department_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind_id, pconn);
    //执行sql语句
    execute_sql(stmt, &bind_id, pconn);

    //获取sql语句的结果集
    //先获取元数据信息及字段信息
    MYSQL_RES* res = show_result_st(pconn, stmt);
    //设置输出参数 ,后续每行的数据都放在的参数中
    int id_res;
    char d_name_res[50] = {0};
    char dept_name_res[50] = {0};
    MYSQL_TIME date_res;
    memset(&date_res, 0, sizeof(date_res));

    char shift_res[50] = {0};

    // 每个参数对应一个bind_res
    MYSQL_BIND bind_res[5];

    memset(bind_res, 0, sizeof(bind_res));
    //参数绑定
    bind_tie_out(&bind_res[0], &id_res, sizeof(int), MYSQL_TYPE_LONG);
    bind_tie_out(&bind_res[1], d_name_res, sizeof(d_name_res), MYSQL_TYPE_VAR_STRING);
    bind_tie_out(&bind_res[2], dept_name_res, sizeof(dept_name_res), MYSQL_TYPE_VAR_STRING);
    bind_tie_out(&bind_res[3], (char*)&date_res, sizeof(date_res), MYSQL_TYPE_DATE);
    bind_tie_out(&bind_res[4], shift_res, sizeof(shift_res), MYSQL_TYPE_VAR_STRING);

    bind_param(stmt, bind_res, pconn);

    ret = mysql_stmt_bind_result(stmt, bind_res);
    if(ret)
    {
        stmt_check_safe(stmt, pconn);
    }
    //得到数据之后进行缓冲处理，后面使用mysql_stmt_fetch获取行数据
    ret = mysql_stmt_store_result(stmt);
    if(ret)
    {
        stmt_check_safe(stmt, pconn);
    }
    //真正的获取数据
    while(1)
    {
        int status = mysql_stmt_fetch(stmt);
        if(status == 1 || status == MYSQL_NO_DATA)
        {
            break;
        }
        printf("%d\t%s\t%s\t%04u-%02u-%02u\t%s\n", id_res, d_name_res, dept_name_res, date_res.year, date_res.month, date_res.day, shift_res);
    }
    mysql_free_result(res);
    mysql_stmt_close(stmt);

    //打印表格之后，用户输入排班计划，查看排班计划是否存在，存在选择成功
    printf("请输入要挂号的ID:");
    int s_id = 0;
    int_in(&s_id);
    
    //查看排班id是否存在
    const char* dep_s_id_sql = 
        "SELECT s.id AS 值班表ID, d.name AS 医生姓名, dept.name AS 科室名称, "
        "s.date AS 值班日期, s.shift AS 班次 "
        "FROM schedule s "
        "JOIN doctor d ON s.doctor_id = d.id "
        "JOIN department dept ON d.department_id = dept.id "
        "WHERE dept.id = ? AND s.id = ? "
        "ORDER BY s.date";
    //完成映射并将sql语句模板化
    stmt = sql_stl(pconn, dep_s_id_sql, 2);
    MYSQL_BIND sch_bind[2];
    memset(sch_bind, 0, sizeof(sch_bind));
    bind_tie_in(&sch_bind[0], &department_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&sch_bind[1], &s_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, sch_bind, pconn);
    
    //执行sql语句
    execute_sql(stmt, sch_bind, pconn);

    //查看是否存在该科室
    ret = sql_if_res(stmt);
    //不存在就返回
    if(ret)
    {
        return;
    }
    mysql_stmt_close(stmt);

    //用户需要输入的参数基本完成，准备添加操作
    const char* add_sql = "insert into registrations (patient_id, schedule_id, priority) values(?,?,?)";
    stmt = sql_stl(pconn, add_sql, 3);
    
    // 每一个参数对应一个bind,定义参数
    MYSQL_BIND bind_add[3];

    char priotity[20] = {0};
    unsigned long priotity_len = 0;

    //绑定参数
    memset(bind_add, 0, sizeof(bind_add));

    bind_tie_in(&bind_add[0], &patient_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&bind_add[1], &s_id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_tie_in(&bind_add[2], priotity, &priotity_len, NULL, MYSQL_TYPE_VAR_STRING);

    bind_param(stmt, bind_add, pconn);

    //接收用户参数 传递参数

    printf("请输入挂号等级（eg:普通、紧急）：");
    char_in(priotity, &priotity_len);

    execute_sql(stmt, bind_add, pconn);
    printf("挂号成功！");
    mysql_stmt_close(stmt);
}

void view_registration(MYSQL* pconn)
{
    // 查看挂号表，可以查看挂号id, 就诊人，医生姓名，科室名称，挂号时间，号码等级，号码所处状态
    const char* sql= 
        "SELECT r.id AS 挂号表ID, p.name AS 就诊人姓名, d.name AS 医生姓名, "
        "dept.name AS 科室名称, r.registration_time AS 挂号时间, "
        "r.priority AS 号码等级, r.status AS 号码状态 "
        "FROM registrations r "
        "JOIN patient p ON r.patient_id = p.id "
        "JOIN schedule s ON r.schedule_id = s.id "
        "JOIN doctor d ON s.doctor_id = d.id "
        "JOIN department dept ON d.department_id = dept.id "
        "ORDER BY r.registration_time DESC";
    //发送sql语句
    mysql_query(pconn, sql);
    show_result_ust(pconn);
}
void delete_registration(MYSQL* pconn)
{
    int id;
    printf("请输入要删除挂号记录的就诊人的ID：");
    int_in(&id);

    //检查是否存在
    const char* sql = "select id from registrations where patient_id = ?";
    //完成映射并将sql语句模板化
    MYSQL_STMT* stmt = sql_stl(pconn, sql, 1);

    //参数的定义
    MYSQL_BIND bind;
    memset(&bind, 0, sizeof(bind));

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    //若不存在就返回

    if(sql_if_res(stmt))    return;

    mysql_stmt_free_result(stmt);
    mysql_stmt_close(stmt);

    //准备删除
    const char* sql_del = "delete from registrations where patient_id=?";
    //完成映射并将sql语句模板化
    stmt = sql_stl(pconn, sql_del, 1);

    //id参数已经由用户输入
    //参数绑定
    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);
    printf("删除ID为%d就诊人的挂号信息 成功！\n", id);
    mysql_stmt_close(stmt);
}

//优先队列
void manage_queue(MYSQL* pconn)
{
    int choice;
    do {
        printf("\n===== 队列管理 =====\n");
        printf("1. 处理下一位患者\n");
        printf("2. 查看等待队列\n");
        printf("3. 刷新队列状态\n");
        printf("0. 返回上一级菜单\n");
        printf("=====================\n");
        printf("请输入你的选择: ");
        
        if (scanf("%d", &choice) != 1) {
            printf("输入无效，请输入数字！\n");
            while(getchar() != '\n');  // 清除输入缓冲区
            continue;
        }
        while(getchar() != '\n');  // 清除换行符
        
        switch(choice) {
            case 1:
                process_queue(pconn);
                break;
            case 2:
                list_queue(pconn);
                break;
            case 3:
                printf("队列状态已刷新！\n");
                break;
            case 0:
                printf("返回上一级菜单...\n");
                break;
            default:
                printf("无效的选择，请重新输入！\n");
        }
    } while(choice != 0);
}

// 处理队列中的患者
void process_queue(MYSQL* pconn)
{
    const char* sql =
                    "SELECT "
                    "r.id AS 挂号表ID, "
                    "p.name AS 就诊人姓名, "
                    "d.name AS 医生姓名, "
                    "dept.name AS 科室名称, "
                    "r.registration_time AS 挂号时间, "
                    "r.priority AS 号码等级, "
                    "r.status AS 号码状态 "
                    "FROM registrations r "
                    "JOIN patient p ON r.patient_id = p.id "
                    "JOIN schedule s ON r.schedule_id = s.id "
                    "JOIN doctor d ON s.doctor_id = d.id "
                    "JOIN department dept ON d.department_id = dept.id "
                    "WHERE r.status = '等待就诊' "
                    "ORDER BY CASE WHEN r.priority = '紧急' THEN 0 "
                    "WHEN r.priority = '普通' THEN 1 "
                    "ELSE 2 END, "
                    "r.registration_time ASC "
                    "LIMIT 1";
    mysql_query(pconn, sql);
    MYSQL_RES * res = mysql_store_result(pconn);
    if(res == NULL || mysql_num_rows(res) == 0)
    {
        printf("当前没有等待的患者\n");
        if(res)
        {
            mysql_free_result(res);
            return;
        }
    }

    MYSQL_ROW row = mysql_fetch_row(res);
    printf("\n请 %s 号患者 %s 到 %s 科室 %s 医生诊室就诊（%s %s）\n", row[0], row[1], row[3], row[2], row[5], row[6]);
    
    // 确认是否处理该患者
    char confirm;
    printf("是否确认处理该患者? (y/n): ");
    scanf(" %c", &confirm);
    while(getchar() != '\n');  // 清除换行符
    
    if (confirm != 'y' && confirm != 'Y') {
        printf("已取消处理！\n");
        mysql_free_result(res);
        return;
    }

    //更新状态为正在就诊
    const char* update_sql = "UPDATE registrations SET status = '正在就诊' WHERE id = ?";
    MYSQL_STMT* stmt = sql_stl(pconn, update_sql, 1);

    MYSQL_BIND bind;
    int id = atoi(row[0]);

    bind_tie_in(&bind, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind, pconn);
    //执行sql语句
    execute_sql(stmt, &bind, pconn);

    printf("患者 %s 状态已更新为'正在就诊'\n", row[1]);
    mysql_stmt_close(stmt);
    
    //等待医生完成就诊更新状态
    printf("\n就诊完成后按任意键继续...");
    getchar();
    
    //更新状态为完成就诊
    const char* update_sql2 = "UPDATE registrations SET status = '已完成' WHERE id = ?";
    stmt = sql_stl(pconn, update_sql2, 1);
    
    MYSQL_BIND bind2;
    
    bind_tie_in(&bind2, &id, NULL, NULL, MYSQL_TYPE_LONG);
    bind_param(stmt, &bind2, pconn);
    //执行sql语句
    execute_sql(stmt, &bind2, pconn);
    printf("患者 %s 状态已更新为'已完成'\n", row[1]);
    
    mysql_free_result(res);
}

void list_queue(MYSQL* pconn)
{
    const char* sql = 
                "SELECT "
                "r.id AS 挂号表ID, "
                "p.name AS 就诊人姓名, "
                "d.name AS 医生姓名, "
                "dept.name AS 科室名称, "
                "r.registration_time AS 挂号时间, "
                "r.priority AS 号码等级, "
                "r.status AS 号码状态 "
                "FROM registrations r "
                "JOIN patient p ON r.patient_id = p.id "
                "JOIN schedule s ON r.schedule_id = s.id "
                "JOIN doctor d ON s.doctor_id = d.id "
                "JOIN department dept ON d.department_id = dept.id "
                "WHERE r.status = '等待就诊' "
                "ORDER BY CASE WHEN r.priority = '紧急' THEN 0 "
                "WHEN r.priority = '普通' THEN 1 "
                "ELSE 2 END, "
                "r.registration_time ASC ";
    mysql_query(pconn, sql);
    show_result_ust(pconn);

}